use master
go
create database TestDB
on
(
name='TestDB',
filename='D:\SQL\TestDB.mdf'
)
log on
(
name='TestDB_log',
filename='D:\SQL\TestDB_log.ldf'
)
use TestDB
go
create table typeInfo
(
typeId int primary key identity(1,1),
typeName varchar(10) not null,
)
create table loginInfo
(
LoginId int primary key identity(1,1),
LoginName varchar(10) unique not null,
LoginPwd varchar(20) not null default(123456),
LogSex char(1) default('') check(LogSex='' or LogSex='Ů'),
Logbrithday datetime,
huiyuan  varchar(10)
)
create database company
on
(
name='company',
filename='D:\SQL\company.mdf'
)
log on
(
name='company_log',
filename='D:\SQL\company_log.ldf'
)
use company
go
create table sectionInfo
(
   sectionID  int primary key,
   sectionName  varchar(10) not null
)
create table userInfo
(
  userNo  int primary key not null,
   userName  varchar(10) unique not null check(len(userName)>4),
    userSex   varchar(2) not null check(userSex='' or userSex='Ů' ),
    userAge   int  not null check(userAge>=1 or userAge<=100),
	userAddress  varchar(50) default(''),
	userSection  int constraint FK_sectionInfo_section references sectionInfo(sectionID)
)
create table workInfo
(
   workId int primary key not null,
   userId  int constraint FK_userInfo_userNo references userInfo(userNo),
   workTime datetime not null,
   workDescription varchar(40)  not null check(workDescription='ٵ'or workDescription=''or workDescription='' or workDescription=''or workDescription='¼')
)
create database Student
on
(
name='Student',
filename='D:\SQL\Student.mdf'
)
log on
(
name='Student_log',
filename='D:\SQL\Student_log.ldf'
)
use Student
go
create table Classinfo
(
  classid int primary key,
  classname varchar(10) not null unique,
  classremark ntext
)
create table studentinfo
(
 stunum int primary key,
 stuname varchar check (len(stuname)>2) unique,
 stusex  varchar(2) default('') check(stusex='' or stusex='Ů'),
 stuage  int check(stuage>=15 or stuage<=40) not null,
 stuaddress nvarchar(50) default('人'),
 stuclassid int constraint FK_Classinfo_classid references Classinfo(classid)
)
create table information
(
 number int primary key,
 kechenbiao varchar unique not null,
 miaoshu  ntext
)
create table score
(
  scorenum int  primary key,
  scorestuid int constraint FK_studentinfo_stunum references studentinfo(stunum) not null,
  scoreid int constraint FK_information_number references information(number) not null,
  score int check(score>=0 or score<=100)
)
create database fangwuchuzhu
on
(
name='fangwuchuzhu',
filename='D:\SQL\fangwuchuzhu.mdf'
)
log on
(
name='fangwuchuzhu_log',
filename='D:\SQL\fangwuchuzhu_log.ldf'
)
use fangwuchuzhu
go
create table tblUser 
(
 userId int primary key identity(1,1),
 userName varchar,
 userTel  nvarchar(10)
)
create table tblHouseType 
(
 typeId  int primary key identity(1,1),
 typName varchar check(typName='ݷ1'or typName='ݷ2'or typName='ݷ3' or typName='ݷ4') not null
)
create table tblQx 
(
 qxId int primary key identity(1,1),
 qxName varchar check(qxName=''or qxName='' or qxName='') not null
)

create table tblHouseInfo
(
  id int primary key identity(1,1),
  userId int constraint FK_tblUser_userId references tblUser(userId),
  zj money,
  shi varchar(20),
  ting int,
  typeId int constraint FK_tblHouseType_typeId references tblHouseType(typeId),
  qxId int constraint FK_tblQx_qxId references tblQx(qxId)
)